/**********************************************************************************************************************
 * SplendidCRM is a Customer Relationship Management program created by SplendidCRM Software, Inc. 
 * Copyright (C) 2005-2011 SplendidCRM Software, Inc. All rights reserved.
 * 
 * This program is free software: you can redistribute it and/or modify it under the terms of the 
 * GNU Affero General Public License as published by the Free Software Foundation, either version 3 
 * of the License, or (at your option) any later version.
 * 
 * This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; 
 * without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. 
 * See the GNU Affero General Public License for more details.
 * 
 * You should have received a copy of the GNU Affero General Public License along with this program. 
 * If not, see <http://www.gnu.org/licenses/>. 
 * 
 * You can contact SplendidCRM Software, Inc. at email address support@splendidcrm.com. 
 * 
 * In accordance with Section 7(b) of the GNU Affero General Public License version 3, 
 * the Appropriate Legal Notices must display the following words on all interactive user interfaces: 
 * "Copyright (C) 2005-2011 SplendidCRM Software, Inc. All rights reserved."
 *********************************************************************************************************************/
using System;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Diagnostics;

namespace SplendidCRM.Campaigns
{
	/// <summary>
	/// Summary description for DetailView.
	/// </summary>
	public partial class DetailView : SplendidControl
	{
		protected _controls.ModuleHeader   ctlModuleHeader ;
		protected _controls.DynamicButtons ctlDynamicButtons;
		protected _controls.DynamicButtons ctlDynamicLinks;
	    protected DataView                 ContactsView;

        private DataTable dtTeams, dtCheckedTeams;
        private DataTable tblCheckedTeamValue;

		protected Guid        gID              ;

		public class SendMail
		{
			private HttpContext Context;
			private Guid        gID    ;
			
			public SendMail(HttpContext Context, Guid gID)
			{
				this.Context = Context;
				this.gID     = gID    ;
			}
			
			// 06/16/2011 Paul.  Placing the emails in queue can take a long time, so place into a thread. 
			public void Start()
			{
				try
				{
					SplendidError.SystemMessage(Context, "Warning", new StackTrace(true).GetFrame(0), "Campaign Start: " + gID.ToString() + " at " + DateTime.Now.ToString() );
					if ( !Sql.IsEmptyGuid(gID) )
					{
						Context.Application["Campaigns." + gID.ToString() + ".Sending"] = true;
						DbProviderFactory dbf = DbProviderFactories.GetFactory(Context.Application);
						using ( IDbConnection con = dbf.CreateConnection() )
						{
							con.Open();
							using ( IDbTransaction trn = Sql.BeginTransaction(con) )
							{
								try
								{
									// 08/22/2011 Paul.  We need to use the command object so that we can increase the timeout. 
									//SqlProcs.spCAMPAIGNS_SendEmail(gID, false, trn);
									using ( IDbCommand cmdCAMPAIGNS_SendEmail = SqlProcs.cmdCAMPAIGNS_SendEmail(con) )
									{
										cmdCAMPAIGNS_SendEmail.Transaction    = trn;
										cmdCAMPAIGNS_SendEmail.CommandTimeout = 0;
										Sql.SetParameter(cmdCAMPAIGNS_SendEmail, "@ID"              , gID             );
										Sql.SetParameter(cmdCAMPAIGNS_SendEmail, "@MODIFIED_USER_ID", Security.USER_ID);
										Sql.SetParameter(cmdCAMPAIGNS_SendEmail, "@TEST"            , false           );
										cmdCAMPAIGNS_SendEmail.ExecuteNonQuery();
									}
									trn.Commit();
								}
								catch
								{
									trn.Rollback();
									throw;
								}
							}
						}
					}
					else
					{
						SplendidError.SystemMessage(Context, "Error", new StackTrace(true).GetFrame(0), "Invalid Campaign ID.");
					}
				}
				catch(Exception ex)
				{
					SplendidError.SystemMessage(Context, "Error", new StackTrace(true).GetFrame(0), Utils.ExpandException(ex));
				}
				finally
				{
					SplendidError.SystemMessage(Context, "Warning", new StackTrace(true).GetFrame(0), "Campaign End: " + gID.ToString() + " at " + DateTime.Now.ToString() );
					Context.Application.Remove("Campaigns." + gID.ToString() + ".Sending");
				}
			}
		}

		protected void Page_Command(Object sender, CommandEventArgs e)
		{
			try
			{
				if ( e.CommandName == "Edit" )
				{
					Response.Redirect("edit.aspx?ID=" + gID.ToString());
				}
				else if ( e.CommandName == "Duplicate" )
				{
					Response.Redirect("edit.aspx?DuplicateID=" + gID.ToString());
				}
				else if ( e.CommandName == "Delete" )
				{
					SqlProcs.spCAMPAIGNS_Delete(gID);
					Response.Redirect("default.aspx");
				}
				else if ( e.CommandName == "SendTest"  )
				{
					SqlProcs.spCAMPAIGNS_SendEmail(gID, true);
					// 12/22/2007 Paul.  Send all queued emails, but include the date so that only these will get sent. 
					EmailUtils.SendQueued(HttpContext.Current, Guid.Empty, gID, false);
					Response.Redirect("view.aspx?ID=" + gID.ToString());
				}
				else if ( e.CommandName == "SendEmail" )
				{
					// 02/22/2011 Paul.  Prevent the user from Campaign running twice. 
					if ( !Sql.ToBoolean(Application["Campaigns." + gID.ToString() + ".Sending"]) )
					{
						// 06/16/2011 Paul.  Placing the emails in queue can take a long time, so place into a thread. 
						// 08/22/2011 Paul.  We need to use a class so that we can pass the context and the ID. 
						SendMail send = new SendMail(this.Context, gID);
						System.Threading.Thread t = new System.Threading.Thread(send.Start);
						t.Start();
						// 08/22/2011 Paul.  The SendEmail thread will be aborted if we redirect the page. 
						ctlDynamicButtons.ErrorText = L10n.Term("Campaigns.LBL_SENDING");
					}
					else
					{
						ctlDynamicButtons.ErrorText = L10n.Term("Campaigns.ERR_SENDING_NOW");
					}
				}
				// 05/14/201 Paul.  Add support for mail merge.
				else if ( e.CommandName == "MailMerge" )
				{
					Response.Redirect("~/MailMerge/default.aspx?Module=" + m_sMODULE + "&chkMain=" + gID.ToString(), true);
				}
				else if ( e.CommandName == "Cancel" )
				{
					Response.Redirect("default.aspx");
				} else if (e.CommandName == "SelectAll")
                {
                    if (ContactsView == null)
                        ContactsView = new DataView(GetCampaignContacts());
                    ctlCheckAll.SelectAll(ContactsView, "ID");
                    grdContactList.DataSource = ContactsView;
                    grdContactList.DataBind();
                }
			}
			catch(Exception ex)
			{
				SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
				ctlDynamicButtons.ErrorText = ex.Message;
			}
		}

		protected void Page_Load(object sender, System.EventArgs e)
		{
			SetPageTitle(L10n.Term(".moduleList." + m_sMODULE));
			// 06/04/2006 Paul.  Visibility is already controlled by the ASPX page, but it is probably a good idea to skip the load. 
			this.Visible = (SplendidCRM.Security.GetUserAccess(m_sMODULE, "view") >= 0);
			if ( !this.Visible )
				return;

			try
			{
				gID = Sql.ToGuid(Request["ID"]);
				// 11/28/2005 Paul.  We must always populate the table, otherwise it will disappear during event processing. 
				// 03/19/2008 Paul.  Place AppendDetailViewFields inside OnInit to avoid having to re-populate the data. 
				if ( !IsPostBack )
				{
					if ( !Sql.IsEmptyGuid(gID) )
					{
						DbProviderFactory dbf = DbProviderFactories.GetFactory();
						using ( IDbConnection con = dbf.CreateConnection() )
						{
							string sSQL ;
							sSQL = "select *               " + ControlChars.CrLf
							     + "  from vwCAMPAIGNS_Edit" + ControlChars.CrLf;
							using ( IDbCommand cmd = con.CreateCommand() )
							{
								cmd.CommandText = sSQL;
								// 11/24/2006 Paul.  Use new Security.Filter() function to apply Team and ACL security rules.
								Security.Filter(cmd, m_sMODULE, "view");
								Sql.AppendParameter(cmd, gID, "ID", false);
								con.Open();

								if ( bDebug )
									RegisterClientScriptBlock("SQLCode", Sql.ClientScriptBlock(cmd));

								// 11/22/2010 Paul.  Convert data reader to data table for Rules Wizard. 
								using ( DbDataAdapter da = dbf.CreateDataAdapter() )
								{
									((IDbDataAdapter)da).SelectCommand = cmd;
									using ( DataTable dtCurrent = new DataTable() )
									{
										da.Fill(dtCurrent);
										if ( dtCurrent.Rows.Count > 0 )
										{
											DataRow rdr = dtCurrent.Rows[0];
											// 11/11/2010 Paul.  Apply Business Rules. 
											this.ApplyDetailViewPreLoadEventRules(m_sMODULE + "." + LayoutDetailView, rdr);
											
											ctlModuleHeader.Title = Sql.ToString(rdr["NAME"]);
											SetPageTitle(L10n.Term(".moduleList." + m_sMODULE) + " - " + ctlModuleHeader.Title);
											Utils.UpdateTracker(Page, m_sMODULE, gID, ctlModuleHeader.Title);
											
											this.AppendDetailViewFields(m_sMODULE + "." + LayoutDetailView, tblMain, rdr);

											string sCAMPAIGN_TYPE = Sql.ToString(rdr["CAMPAIGN_TYPE"]);
											//ctlDynamicButtons.ShowMailMerge = (sCAMPAIGN_TYPE == "Email");
											// 03/20/2008 Paul.  Dynamic buttons need to be recreated in order for events to fire. 
											// 04/28/2008 Paul.  We will need the ASSIGNED_USER_ID in the sub-panels. 
											Page.Items["ASSIGNED_USER_ID"] = Sql.ToGuid(rdr["ASSIGNED_USER_ID"]);
											ctlDynamicButtons.AppendButtons(m_sMODULE + "." + LayoutDetailView, Sql.ToGuid(rdr["ASSIGNED_USER_ID"]), rdr);
											// 04/04/2008 Paul.  Add the ROI links.
											ctlDynamicLinks  .AppendButtons(m_sMODULE + ".LinkView"  , Sql.ToGuid(rdr["ASSIGNED_USER_ID"]), rdr);
											// 01/10/2010 Paul.  MailMerge is not supported at this time. 
											// 05/17/2011 Paul.  Enable support for Mail Merge. 
											ctlDynamicButtons.ShowButton("SendTest"  , sCAMPAIGN_TYPE == "Email");
											ctlDynamicButtons.ShowButton("SendEmails", sCAMPAIGN_TYPE == "Email");
											ctlDynamicButtons.ShowButton("MailMerge" , sCAMPAIGN_TYPE == "Mail" || sCAMPAIGN_TYPE == "NewsLetter");
											// 11/10/2010 Paul.  Apply Business Rules. 
											this.ApplyDetailViewPostLoadEventRules(m_sMODULE + "." + LayoutDetailView, rdr);
										}
										else
										{
											// 11/25/2006 Paul.  If item is not visible, then don't show its sub panel either. 
											plcSubPanel.Visible = false;
											
											// 03/20/2008 Paul.  Dynamic buttons need to be recreated in order for events to fire. 
											ctlDynamicButtons.AppendButtons(m_sMODULE + "." + LayoutDetailView, Guid.Empty, null);
											// 04/04/2008 Paul.  Add the ROI links.
											ctlDynamicLinks  .AppendButtons(m_sMODULE + ".LinkView"  , Guid.Empty, null);
											ctlDynamicButtons.DisableAll();
											ctlDynamicLinks  .HideAll();
											ctlDynamicButtons.ErrorText = L10n.Term("ACL.LBL_NO_ACCESS");
										}
									}
								}
							}
						}
					}
					else
					{
						// 03/20/2008 Paul.  Dynamic buttons need to be recreated in order for events to fire. 
						ctlDynamicButtons.AppendButtons(m_sMODULE + "." + LayoutDetailView, Guid.Empty, null);
						// 04/04/2008 Paul.  Add the ROI links.
						ctlDynamicLinks  .AppendButtons(m_sMODULE + ".LinkView"  , Guid.Empty, null);
						ctlDynamicButtons.DisableAll();
						ctlDynamicLinks  .HideAll();
						//ctlDynamicButtons.ErrorText = L10n.Term(".ERR_MISSING_REQUIRED_FIELDS") + "ID";
					}
				}
				// 06/09/2006 Paul.  Remove data binding in the user controls.  Binding is required, but only do so in the ASPX pages. 
				//Page.DataBind();
                if (!Page.IsPostBack)
                {
                    GetFilterTeamList();
                    var tblCampaignContacts = GetCampaignContacts();
                    ContactsView = new DataView(tblCampaignContacts);
                    grdContactList.PageSize = Int16.MaxValue;
                    grdContactList.DataSource = ContactsView;
                    grdContactList.DataBind();
                }
			}
			catch(Exception ex)
			{
				SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex);
				ctlDynamicButtons.ErrorText = ex.Message;
			}
		}

        private DataTable GetCampaignContacts()
        {
            if (Request.QueryString["ID"] == null) return null;
            var campaignId = Sql.ToGuid(Request.QueryString["ID"]);

            DbProviderFactory dbf = DbProviderFactories.GetFactory();
            using (IDbConnection con = dbf.CreateConnection())
            {
                IDbCommand cmdSelectCampaign = con.CreateCommand();
                cmdSelectCampaign.CommandType = CommandType.Text;
                cmdSelectCampaign.CommandText =
@" SELECT cc.ID 'CAMPAIGNS_CONTACT_ID', t.NAME 'ASSIGN_TO_TEAM', cl.* FROM dbo.vwCONTACTS_List cl 
   JOIN dbo.CAMPAIGNS_CONTACTS cc on cc.CONTACT_ID = cl.ID
   LEFT JOIN dbo.TEAMS_CONTACT_ASSIGNATION tca on tca.CAMPAIGN_CONTACT_ID = cc.ID
   LEFT JOIN dbo.TEAMS t on t.ID = tca.TEAM_ID
   where cc.CAMPAIGN_ID = @CAMPAIGN_ID
   and tca.REPLACE_TRANSACTION_ID IS NULL ";
                Sql.AddParameter(cmdSelectCampaign, "@CAMPAIGN_ID", campaignId);
                IDbDataAdapter da = dbf.CreateDataAdapter();
                da.SelectCommand = cmdSelectCampaign;
                var ds = new DataSet();
                da.Fill(ds);

                return ds.Tables[0];
            }
        }

		#region Web Form Designer generated code
		override protected void OnInit(EventArgs e)
		{
			//
			// CODEGEN: This call is required by the ASP.NET Web Form Designer.
			//
			InitializeComponent();
			base.OnInit(e);
		}
		
		/// <summary>
		/// Required method for Designer support - do not modify
		/// the contents of this method with the code editor.
		/// </summary>
		private void InitializeComponent()
		{
            ctlCheckAll.Command += new CommandEventHandler(Page_Command);
			ctlDynamicButtons.Command += new CommandEventHandler(Page_Command);
			m_sMODULE = "Campaigns";
			SetMenu(m_sMODULE);
			this.AppendDetailViewRelationships(m_sMODULE + "." + LayoutDetailView, plcSubPanel);
			if ( IsPostBack )
			{
				this.AppendDetailViewFields(m_sMODULE + "." + LayoutDetailView, tblMain, null);
				// 03/20/2008 Paul.  Dynamic buttons need to be recreated in order for events to fire. 
				ctlDynamicButtons.AppendButtons(m_sMODULE + "." + LayoutDetailView, Guid.Empty, null);
				// 04/04/2008 Paul.  Add the ROI links.
				ctlDynamicLinks  .AppendButtons(m_sMODULE + ".LinkView"  , Guid.Empty, null);
			}
		}
		#endregion



        //Get filter column list, create DataTable dtTeams & dtCheckedTeams
        private void GetFilterTeamList()
        {
            dtTeams = GetTeamList();

            var vwColumns = new DataView(dtTeams);
            vwColumns.Sort = "NAME";

            dtCheckedTeams = dtTeams.Clone();

            cltSearchTeamList.DataTextField = "Name";
            cltSearchTeamList.DataValueField = "ID";
            cltSearchTeamList.DataSource = vwColumns;
            cltSearchTeamList.DataBind();

            cltCheckedTeamRepeater.DataSource = dtCheckedTeams;
            cltCheckedTeamRepeater.DataBind();

            ViewState["dtTeams"] = dtTeams;
            ViewState["dtCheckedTeams"] = dtCheckedTeams;
        }

	    private DataTable GetTeamList()
	    {
			DbProviderFactory dbf = DbProviderFactories.GetFactory();
			using ( IDbConnection con = dbf.CreateConnection() )
			{
				IDbCommand cmdSelectTeam = con.CreateCommand();
				cmdSelectTeam.CommandType = CommandType.Text;
				cmdSelectTeam.CommandText =
@" SELECT ID, NAME, 0 'PRIORITY' FROM dbo.TEAMS WHERE DELETED = 0 ORDER BY NAME  ";
				IDbDataAdapter da = dbf.CreateDataAdapter();
				da.SelectCommand = cmdSelectTeam;
                DataSet ds = new DataSet();
				da.Fill(ds);

                return ds.Tables[0];
			}
	    }

        protected void cltAddTeam_OnClick(object sender, EventArgs e)
        {

            if (cltSearchTeamList.SelectedIndex > -1)
            {
                dtTeams = ViewState["dtTeams"] as DataTable;
                dtCheckedTeams = ViewState["dtCheckedTeams"] as DataTable;

                var selectedValue = cltSearchTeamList.SelectedValue;
                DataRow[] rows = dtTeams.Select("ID='" + selectedValue + "'");
                if (rows.Length == 0) return;
                DataRow selectedRow = dtCheckedTeams.NewRow();
                selectedRow["ID"] = rows[0]["ID"].ToString();
                selectedRow["NAME"] = rows[0]["NAME"].ToString();
                selectedRow["PRIORITY"] = dtCheckedTeams.Rows.Count + 1;
                dtCheckedTeams.Rows.Add(selectedRow);
                dtTeams.Rows.Remove(rows[0]);

                DataView vwColumns = new DataView(dtTeams);
                vwColumns.Sort = "NAME";

                cltSearchTeamList.DataTextField = "NAME";
                cltSearchTeamList.DataValueField = "ID";
                cltSearchTeamList.DataSource = vwColumns;
                cltSearchTeamList.DataBind();

                cltCheckedTeamRepeater.DataSource = dtCheckedTeams;
                cltCheckedTeamRepeater.DataBind();
            }
        }

	    protected void cltCheckedTeamRepeater_OnItemCommand(object source, RepeaterCommandEventArgs e)
        {
            if (e.CommandName == "RemoveColumn")
            {
                var teamId = e.CommandArgument.ToString();
                dtTeams = ViewState["dtTeams"] as DataTable;
                dtCheckedTeams = ViewState["dtCheckedTeams"] as DataTable;

                DataRow[] rows = dtCheckedTeams.Select("ID='" + teamId + "'");
                if (rows.Length == 0) return;
                DataRow newRow = dtTeams.NewRow();
                newRow["ID"] = rows[0]["ID"].ToString();
                newRow["NAME"] = rows[0]["NAME"].ToString();
                dtTeams.Rows.Add(newRow);
                var removedRowPriority = (int)rows[0]["PRIORITY"];
                dtCheckedTeams.Rows.Remove(rows[0]);
                foreach (DataRow row in dtCheckedTeams.Rows)
                {
                    if ((int) row["PRIORITY"] > removedRowPriority)
                    {
                        row["PRIORITY"] = (int) row["PRIORITY"] - 1;
                    }
                }

                DataView vwColumns = new DataView(dtTeams);
                vwColumns.Sort = "NAME";

                cltSearchTeamList.DataTextField = "NAME";
                cltSearchTeamList.DataValueField = "ID";
                cltSearchTeamList.DataSource = vwColumns;
                cltSearchTeamList.DataBind();

                DataView vwCheckedTeams = new DataView(dtCheckedTeams);
                vwCheckedTeams.Sort = "PRIORITY";

                cltCheckedTeamRepeater.DataSource = vwCheckedTeams;
                cltCheckedTeamRepeater.DataBind();
            } else if (e.CommandName == "PriorityUp")
            {
                var teamId = e.CommandArgument.ToString();
                dtTeams = ViewState["dtTeams"] as DataTable;
                dtCheckedTeams = ViewState["dtCheckedTeams"] as DataTable;

                DataRow[] rows = dtCheckedTeams.Select("ID='" + teamId + "'");
                if (rows.Length == 0) return;
                var processedRowPriority = (int)rows[0]["PRIORITY"];
                if (processedRowPriority <= 1)
                    return;
                foreach (DataRow row in dtCheckedTeams.Rows)
                {
                    if ((int) row["PRIORITY"] == processedRowPriority - 1)
                    {
                        row["PRIORITY"] = processedRowPriority;
                    }
                }
                rows[0]["PRIORITY"] = processedRowPriority - 1;

                DataView vwColumns = new DataView(dtTeams);
                vwColumns.Sort = "NAME";

                cltSearchTeamList.DataTextField = "NAME";
                cltSearchTeamList.DataValueField = "ID";
                cltSearchTeamList.DataSource = vwColumns;
                cltSearchTeamList.DataBind();

                DataView vwCheckedTeams = new DataView(dtCheckedTeams);
                vwCheckedTeams.Sort = "PRIORITY";

                cltCheckedTeamRepeater.DataSource = vwCheckedTeams;
                cltCheckedTeamRepeater.DataBind();
            } else if (e.CommandName == "PriorityDown")
            {
                var teamId = e.CommandArgument.ToString();
                dtTeams = ViewState["dtTeams"] as DataTable;
                dtCheckedTeams = ViewState["dtCheckedTeams"] as DataTable;

                DataRow[] rows = dtCheckedTeams.Select("ID='" + teamId + "'");
                if (rows.Length == 0) return;
                var processedRowPriority = (int)rows[0]["PRIORITY"];
                if (processedRowPriority >= dtCheckedTeams.Rows.Count)
                    return;
                foreach (DataRow row in dtCheckedTeams.Rows)
                {
                    if ((int)row["PRIORITY"] == processedRowPriority + 1)
                    {
                        row["PRIORITY"] = processedRowPriority;
                    }
                }
                rows[0]["PRIORITY"] = processedRowPriority + 1;

                DataView vwColumns = new DataView(dtTeams);
                vwColumns.Sort = "NAME";

                cltSearchTeamList.DataTextField = "NAME";
                cltSearchTeamList.DataValueField = "ID";
                cltSearchTeamList.DataSource = vwColumns;
                cltSearchTeamList.DataBind();

                DataView vwCheckedTeams = new DataView(dtCheckedTeams);
                vwCheckedTeams.Sort = "PRIORITY";

                cltCheckedTeamRepeater.DataSource = vwCheckedTeams;
                cltCheckedTeamRepeater.DataBind();
            }
        }

	    protected void cltAssignButton_OnClick(object sender, EventArgs e)
	    {
	        var selectedContactIdArray = ctlCheckAll.SelectedItemsArray;
	        var selectedTeamArray = cltHiddenCheckedTeamValue.Value.Split(',');
	        var assignType = ctlAssignType.SelectedValue.ToString();
            if (selectedContactIdArray.Length > 0 && selectedTeamArray.Length > 0)
            {
                var selectedTeamList = cltHiddenCheckedTeamValue.Value + ",";
                var selectedContactIdList = selectedContactIdArray.Aggregate("", (current, t) => current + (t + ","));
                var result = AssignContactToTeams(selectedContactIdList, selectedTeamList, assignType);
                if (result != Guid.Empty)
                {
                    Server.Transfer("AssignTeamContactReport.aspx?RedirectId=" + gID + "&TransactionId=" + result.ToString());
                    return;
                }
                ScriptManager.RegisterStartupScript(this, typeof(Page), "script", "alert('Assign failed!');", true);
            }

            ScriptManager.RegisterStartupScript(this, typeof(Page), "script", "SplendidGrid_CheckAll(0);", true);
	    }

        private Guid AssignContactToTeams(string contactIdList, string teamIdList, string assignType)
        {
            try
            {
                DbProviderFactory dbf = DbProviderFactories.GetFactory();
                using (IDbConnection con = dbf.CreateConnection())
                {
                    con.Open();
                    IDbCommand cmd = con.CreateCommand();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "spASSIGN_CONTACT_TO_TEAMS";
                    Sql.AddParameter(cmd, "CONTACT_ID_LIST", contactIdList);
                    Sql.AddParameter(cmd, "TEAM_ID_LIST", teamIdList);
                    Sql.AddParameter(cmd, "ASSIGN_TYPE", assignType);
                    var pTransactionId = cmd.CreateParameter();
                    pTransactionId.ParameterName = "TRANSACTION_ID";
                    pTransactionId.DbType = DbType.Guid;
                    pTransactionId.Direction = ParameterDirection.Output;

                    cmd.Parameters.Add(pTransactionId);
                    cmd.ExecuteNonQuery();

                    return (Guid) pTransactionId.Value;
                }
            }
            catch (Exception ex)
            {
                Debugger.Break();
                return Guid.Empty;
            }

        }
	}
}


